package com.inviscidlabs.schooled;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class GradeDB extends SQLiteOpenHelper{

	private static final String DATABASE="schooledDB";
	private static final int DATABASE_VERSION=1;
			
	//Table Variables
	public static final String TABLE_SEMESTERS="semesterTable";
	public static final String TABLE_CLASSES="classTable";
	public static final String TABLE_GRADETYPES="typeTable";
	public static final String TABLE_GRADES="gradeTable";
	
	//Semester Fields
	public static final String KEY_SEMID="_id";
	public static final String KEY_SEMNAME="semesterName";
	
	//Class Fields
	public static final String KEY_CLASSID="_id";
	public static final String KEY_CLASSNAME="className";
	//TODO NEED TO ADD
	public static final String KEY_CLASSEMAIL="classProfessorEmail";
	public static final String KEY_CLASSSCORE="classScore";
	public static final String KEY_CLASSPOSSIBLE="classPossible";
	public static final String KEY_CLASSSEMESTER="semesterOfClass";
	
	//GradeType Fields
	public static final String KEY_TYPEID="_id";
	public static final String KEY_TYPENAME="typeName";
	public static final String KEY_TYPECLASS="classOfType";
	public static final String KEY_TYPESCORE="typeScore";
	public static final String KEY_TYPEPOSSIBLE="typePossible";
	public static final String KEY_TYPEATTENDANCE="typeAttendance";
	
	//Grade Fields
	public static final String KEY_GRADEID="_id";
	public static final String KEY_GRADENAME="gradeName";
	public static final String KEY_GRADESCORE="gradeScore";
	public static final String KEY_GRADEPOSSIBLE="gradePossible";
	public static final String KEY_GRADECLASS="classOfGrade";
	public static final String KEY_GRADETYPE="typeOfGrade";
	
	
	
	
	
//===========SQL Create Strings=================
	
	public static final String CREATE_SEMESTERTABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_SEMESTERS +
					" (" + KEY_SEMID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_SEMNAME + " TEXT);";
	
	public static final String CREATE_CLASSTABLE="CREATE TABLE IF NOT EXISTS " + TABLE_CLASSES + " ("
					+ KEY_CLASSID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_CLASSEMAIL + " TEXT, " + KEY_CLASSNAME + " TEXT, " +
					 KEY_CLASSSCORE + " REAL, " + KEY_CLASSPOSSIBLE +
					" REAL, " + KEY_CLASSSEMESTER + " INTEGER NOT NULL, FOREIGN KEY(" + KEY_SEMID +
					") REFERENCES " + TABLE_SEMESTERS + "(" + KEY_SEMID + "));";
	
	public static final String CREATE_TYPETABLE="CREATE TABLE IF NOT EXISTS " + TABLE_GRADETYPES + " ("
					+ KEY_TYPEID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_TYPENAME + " TEXT, " +
					KEY_TYPECLASS + " INTEGER NOT NULL, " + KEY_TYPESCORE + " REAL, " + KEY_TYPEPOSSIBLE +
					" REAL, " + KEY_TYPEATTENDANCE + " INTEGER DEFAULT 0, FOREIGN KEY(" +
					KEY_TYPECLASS + ") REFERENCES " + TABLE_CLASSES + "(" + KEY_CLASSID + "));";
	
	public static final String CREATE_GRADETABLE="CREATE TABLE IF NOT EXISTS " + TABLE_GRADES + " (" +
					KEY_GRADEID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_GRADENAME + " TEXT, " +
					KEY_GRADESCORE + " REAL, " + KEY_GRADEPOSSIBLE + " REAL, " + KEY_GRADECLASS + 
					" INTEGER NOT NULL, " + KEY_GRADETYPE + " INTEGER NOT NULL, FOREIGN KEY(" + KEY_GRADETYPE +
					") REFERENCES " + TABLE_GRADETYPES + "(" + KEY_TYPEID + "));";
		
	//Reusable Cursor (yay)
	private Cursor cU;
	//Null value to avoid memory leak
	private static GradeDB mInstance=null;
	
//=========OVERRIDDE METHODS=============
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_SEMESTERTABLE);
		db.execSQL(CREATE_CLASSTABLE);
		db.execSQL(CREATE_TYPETABLE);
		db.execSQL(CREATE_GRADETABLE);
		
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		onCreate(db);
	}
	
	//Turns on Foreign keys (for cascading deletion)
	@Override
	public void onOpen(SQLiteDatabase db){
		super.onOpen(db);
		db.execSQL("PRAGMA foreign_keys=ON;");
	}
	
//=====Instantiation=========
	public GradeDB(Context context) {
		super(context, DATABASE, null, DATABASE_VERSION);
	}
	
	//======Ensures that only one Database is open at a time
	public static GradeDB getInstance(Context ctx){
		if(mInstance==null){
			mInstance=new GradeDB(ctx.getApplicationContext());
		}
		return mInstance;
		
	}
	
//===========Public Methods===============
	public void closeDbStuff(){
		if(cU!=null){cU.close();}
	}
	
//=========QUERIES============
	
	//=====================SEMESTERS========================
	public void createSemester(String semName){
		ContentValues cv = new 	ContentValues();
		cv.put(KEY_SEMNAME, semName);
		getWritableDatabase().insert(TABLE_SEMESTERS, null, cv);
		getWritableDatabase().close();
	}
	
	public void updateSemester(long sId, String semName){
		ContentValues cv = new ContentValues();
		cv.put(KEY_SEMNAME, semName);
		getWritableDatabase().update(TABLE_SEMESTERS, cv, KEY_SEMID + "=" + sId, null);
		getWritableDatabase().close();
	}
	
	
	public Cursor fetchSemester(long sId){
		cU=getReadableDatabase().query(TABLE_SEMESTERS, new String[]{KEY_SEMID, KEY_SEMNAME},
								KEY_SEMID + "=" + sId, null, null, null, null);
		return cU;	
	}
	
	public void deleteSemester(long sId){
		getWritableDatabase().delete(TABLE_SEMESTERS, KEY_SEMID + "=" + sId, null);
	}
	

	public boolean semesterHasClasses(long sId){
		Cursor c=fetchClassesOfSemester(sId);
		if(c.getCount()==0||c==null){
			return false;
		}else{
			return true;
			}
		
	}
	
	public Cursor fetchAllSemesters(){
		cU=getReadableDatabase().query(TABLE_SEMESTERS, new String[]{KEY_SEMID, KEY_SEMNAME},
								null, null, null, null, KEY_SEMID + " DESC");
		cU.moveToFirst();
		return cU;
	}
	
	//==========================CLASSES==================================
	
	//ContentValues to be reused in upDate/createNew
	private ContentValues classValues(String className, String classEmail){
		ContentValues cv=new ContentValues();	
		cv.put(KEY_CLASSNAME, className); 
		cv.put(KEY_CLASSEMAIL, classEmail);
		
		return cv;		
	}
	
	
	public void createClass(String className, long sId, String classEmail){
		ContentValues cv=classValues(className, classEmail);
		//we only put in classSemester the first time, not going to edit this value
		cv.put(KEY_CLASSSEMESTER, sId);
		getWritableDatabase().insert(TABLE_CLASSES, null, cv);
		getWritableDatabase().close();
		
	}
	
	public void editClass(String className, String classEmail, long classId){
		ContentValues cv = classValues(className, classEmail);
		getWritableDatabase().update(TABLE_CLASSES, cv, KEY_CLASSID + "=" + classId, null);
		getWritableDatabase().close();
	}
	
	public void deleteClass(long cId){
		getWritableDatabase().delete(TABLE_CLASSES, KEY_CLASSID+"="+cId, null);
		getWritableDatabase().close();
	}
	
	public Cursor fetchClassesOfSemester(long semId){
		cU= getWritableDatabase().query(TABLE_CLASSES, new String[]{KEY_CLASSID, KEY_CLASSNAME,
				 KEY_CLASSSCORE, KEY_CLASSSEMESTER, KEY_CLASSEMAIL}, KEY_CLASSSEMESTER+"="+semId, null, null, null, null);
		cU.moveToFirst();
		return cU;
		}
	
	public Cursor fetchClass(long cId){
		cU=getWritableDatabase().query(TABLE_CLASSES, new String[]{KEY_CLASSID, KEY_CLASSNAME,
				KEY_CLASSPOSSIBLE, KEY_CLASSSEMESTER, KEY_CLASSEMAIL}, KEY_CLASSID+"="+cId, null, null, null, null);
		cU.moveToFirst();
		return cU;
	}
		
//================GRADE TYPE QUERIES=============	
	
	private ContentValues typeValues(String typeName, float typeScore, float typePossible, int typeAttend){
		ContentValues cv = new ContentValues();
		cv.put(KEY_TYPENAME, typeName); cv.put(KEY_TYPESCORE, typeScore);
		cv.put(KEY_TYPEPOSSIBLE, typePossible);cv.put(KEY_TYPEATTENDANCE, typeAttend);
		return cv;		
	}
	
	public void createType(String typeName, float typeScore, float typePossible, int typeAttend, long typeClass){
		ContentValues cv=typeValues(typeName, typeScore, typePossible, typeAttend);
		cv.put(KEY_TYPECLASS, typeClass);
		getWritableDatabase().insert(TABLE_GRADETYPES, null, cv);
	}
	
	public void editType(String typeName, float typeScore, float typePossible, int typeAttend, long typeId){
		ContentValues cv = typeValues(typeName, typeScore, typePossible, typeAttend);
		getWritableDatabase().update(TABLE_GRADETYPES, cv, KEY_TYPEID+"="+typeId, null);
	}
	
	public void deleteType(long typeId){
		getWritableDatabase().delete(TABLE_GRADETYPES, KEY_TYPEID+"="+typeId, null);
	}



}
	
	
	
	
	
	

